﻿#include "home.h"
#include "ui_home.h"
#include <QFileDialog>
#include <QAxObject>
#include <QDebug>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlRecord>
#include <QSqlTableModel>
#include <QListWidget>
#include <QListWidgetItem>
#include <QHeaderView>
#include <QFont>
#include <QList>
#include <QMap>
#include <QDateTime>
#include <QVector>
#define DEFAULTROWS 30

Home::Home(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Home)
{
    ui->setupUi(this);
    init();
}

Home::~Home()
{
    delete ui;
}

void Home::init()
{
    setWindowTitle("成绩记录系统");

    //tablewidget
    ui->tableWidget_0->setEditTriggers(QAbstractItemView::NoEditTriggers);//设置表格内容不可编辑
    ui->tableWidget_0->verticalHeader()->setHidden(false);//隐藏行号列
    ui->tableWidget_0->setHorizontalScrollBarPolicy(Qt::ScrollBarAlwaysOn);//设置水平滚动隐藏
    ui->tableWidget_0->setVerticalScrollBarPolicy(Qt::ScrollBarAlwaysOn);//设置竖直滚动
    ui->tableWidget_0->horizontalHeader()->setSectionResizeMode(QHeaderView::Fixed);//设置表头不可拖动
    ui->tableWidget_0->verticalHeader()->setSectionResizeMode(QHeaderView::Fixed);
    ui->tableWidget_0->setSelectionMode(QAbstractItemView::NoSelection);//设置不可选
    ui->tableWidget_0->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tableWidget_0->verticalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    //列设置
    ui->tableWidget_0->setRowCount(DEFAULTROWS);
    ui->tableWidget_0->setColumnCount(13);
    QStringList header;
    header << "姓名" << "学号" << "语文" << "数学" << "英语" << "物理" << "化学" << "生物" << "政治" << "历史" << "地理" << "班名次" << "校名次";
    ui->tableWidget_0->setHorizontalHeaderLabels(header);

    ui->tableWidget_0->horizontalHeader()->setStyleSheet(
    "QHeaderView::section{background-color:rgb(225,225,225);"
    "color: black;};");
    ui->tableWidget_0->verticalHeader()->setStyleSheet(
    "QHeaderView::section{background-color:rgb(225,225,225);"
    "color: black;};");

    //设置字体格式和大小
    QFont appFont;
    //appFont.setFamily("宋体");
    appFont.setPixelSize(30);
    this->setFont(appFont);
    QFont fLineEdit_0;
    fLineEdit_0.setPixelSize(50);
    ui->lineEdit_0->setFont(fLineEdit_0);

    //建立数据库连接
    createDateBaseConnection();

    c2e.clear();
    c2e.insert("姓名", "name");
    c2e.insert("语文", "Chinese");
    c2e.insert("数学", "Math");
    c2e.insert("英语", "English");
    c2e.insert("物理", "Physics");
    c2e.insert("化学", "Chemical");
    c2e.insert("生物", "Biology");
    c2e.insert("政治", "Politics");
    c2e.insert("历史", "History");
    c2e.insert("地理", "Geography");
    c2e.insert("理综", "Rationale");
    c2e.insert("班名次", "ClassNo");
    c2e.insert("校名次", "SchoolNo");

    ui->checkBox_0->setChecked(true);
    ui->checkBox_1->setChecked(true);
    ui->checkBox_2->setChecked(true);
    ui->checkBox_3->setChecked(true);

}

void Home::on_pushButton_0_clicked()
{
    QVariantList varRowContents = getExcelData();
    if (varRowContents.value(0) == false)
        return;
    loadExcelDataToDataBase(varRowContents);
    return;
}

QVariantList Home::getExcelData()
{
    QString filePath = QFileDialog::getOpenFileName(this, QStringLiteral("选择Excel文件"), "C:\\Users", QStringLiteral("Exel file(*.xls *.xlsx)"));
    if (filePath.isEmpty())
    {
        QVariantList falseReturn;
        falseReturn.append(false);
        return falseReturn;
    }
    QAxObject *excel = new QAxObject(this);
    excel->setControl("Excel.Application");//连接Excel控件
    excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
    excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
    //打开文件
    QAxObject *workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
    workbooks->dynamicCall("Open (const QString&)", filePath);
    QAxObject *workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
    //打开表
    QAxObject *worksheets = workbook->querySubObject("Sheets");//获取工作表集合
    QAxObject *worksheet = worksheets->querySubObject("Item(int)",1);//工作表1，即sheet1
    //获取表中的数据到QVariant中
    QAxObject *usedRange = worksheet->querySubObject("UsedRange");
    QVariant var = usedRange->dynamicCall("value");
    QVariantList varRowContents = var.toList();
    //删除，防止程序关闭后有excel进程残留
    excel->dynamicCall("Quit(void)");
    delete excel;
    return varRowContents;
}

void Home::loadExcelDataToDataBase(QVariantList varRowContents)
{
    QDateTime currentDateTime = QDateTime::currentDateTime();
    QString scurrentDateTime = currentDateTime.toString("yyyy-MM-dd hh:mm:ss");//获得系统当前时间

    bool dataBaseExist = QSqlDatabase::contains("sqlite1");
    if (dataBaseExist == false)
    {
        bool successCreateConnection = createDateBaseConnection();
        if (successCreateConnection == false)
            return;
    }
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    //QStringList tableList = db.tables();

    if (db.tables().contains("idToName") == false)
        createTable("idToName", "id int primary key, name varchar(5)");//创建学号姓名表
    QVariantList header = varRowContents[0].toList();
    int idCol = header.indexOf(QVariant("学号"));
    int nameCol = header.indexOf(QVariant("姓名"));
    QVariantList view;
    view.clear();
    for (int i = 1; i < varRowContents.size(); i++)
    {
        QVariantList nowRow = varRowContents[i].toList();
        QVariantList nowRowData;
        nowRowData.clear();
        nowRowData.append(nowRow[idCol]);
        nowRowData.append(nowRow[nameCol]);
        QVariant nowRowDataToOneLine = nowRowData;
        view.append(nowRowDataToOneLine);
    }
    insertData(view, "idToName");//特别判断是否有学号姓名表

    QStringList fields;//储存该表的所有字段
    QList<int> field_cols;//储存对应字段的索引值
    view.clear();//储存各个表
    for (int i = 0; i < header.size(); i++)
    {
        if (header[i] != "学号" && header[i] != "姓名")
        {
            fields.append(c2e[header[i].toString()]);
            field_cols.append(i);
        }
    }
    for (int i = 0; i < fields.size(); i++)//遍历每一个字段
    {
        for (int j = 1; j < varRowContents.size(); j++)//遍历每一行
        {
            view.clear();
            QVariantList nowRow = varRowContents[j].toList();
            QString tableName = "grades" + nowRow[idCol].toString() + "_" + fields[i];
            if (db.tables().contains(tableName) == false)
                createTable(tableName, QString("%1 double, upDateTime varchar(25)").arg(fields[i]));
            QVariantList nowRowData;
            if (nowRow[field_cols[i]].isNull())
                continue;
            nowRowData.append(nowRow[field_cols[i]]);
            nowRowData.append(QVariant(scurrentDateTime));
            QVariant nowRowDataToOneLine = nowRowData;
            view.append(nowRowDataToOneLine);
            insertData(view, tableName);
        }
    }
    return;
}

bool Home::createDateBaseConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "sqlite1");
    db.setDatabaseName("grade.db");
    if (!db.open())
    {
        qDebug() << "Can't create connection with database!";
        return false;
    }
    return true;
}

bool Home::createTable(QString newTableName, QString Parameters)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
    db.transaction();
    QSqlQuery query(db);
    bool success = query.exec(QString("create table %1(%2);").arg(newTableName).arg(Parameters));
    if (success)
    {
        db.commit();
        qDebug() << QObject::tr("数据库表%1创建成功！").arg(newTableName);
        return true;
    }
    else
    {
        db.rollback();
        QSqlError lastError = query.lastError();
        qDebug() << lastError.driverText() << QString(QObject::tr("数据库表创建失败！"));
        return false;
    }
    return true;
}

bool Home::insertData(QVariantList allData, QString tableName)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    bool beginTransaction = db.transaction();
    if (beginTransaction == true)
        qDebug() << "成功开启事务！";
    else
        qDebug() << "开启事物失败！";
    QList<int> ids;
    QSqlQuery query(db);
    query.exec("select id from idToName");
    query.first();
    query.previous();
    while (query.next())
    {
        ids.append(query.value(0).toInt());
    }
    const int rows = allData.size();
    for (int i = 0; i < rows; i++)
    {
        QVariantList nowRow = allData[i].toList();//取一行，相当于数组
        if (tableName == "idToName" && ids.isEmpty() == false && ids.contains(nowRow[0].toInt()))
            continue;
        if (tableName == "idToName")
            query.prepare(QString("insert into %1 values(?, ?)").arg(tableName));
        else
            query.prepare(QString("insert into %1 values(?, ?)").arg(tableName));
        const int cols = nowRow.size();
        for (int j = 0; j < cols; j++)
            query.bindValue(j, nowRow[j]);
        bool successInserSingleRow = query.exec();
        if (successInserSingleRow == false)
        {
            QSqlError lastError = query.lastError();
            qDebug() << lastError.driverText() << QString(QObject::tr("插入失败"));
            bool removeTransaction = db.rollback();
            if (removeTransaction == true)
                qDebug() << "事务回滚成功！";
            else
                qDebug() << "事务回滚失败！";
            return false;
        }
    }
    bool completeTransaction = db.commit();
    if (completeTransaction == true)
        qDebug() << "事务处理成功！";
    else
        qDebug() << "事务提交失败！";
    return true;
}

bool Home::queryById(QString id, QStringList fields)
{
    if (fields.size() == 0)
        return true;
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);

    ui->tableWidget_0->clearContents();//清除表格中已有内容
    ui->tableWidget_0->setRowCount(DEFAULTROWS);
    ui->tableWidget_0->setColumnCount(fields.size());
    ui->tableWidget_0->setHorizontalHeaderLabels(fields);

    QString name = "";
    if (fields.contains("姓名"))
    {
        query.exec(QString("select name from idToName where id = %1").arg(id));
        query.first();
        name = query.value(0).toString();
    }//获取名字

    QVector<QSqlQuery> allData;
    allData.clear();
    for (int i = 0; i < fields.size(); i++)
    {
        if (fields[i] != "姓名")
        {
            query.exec(QString("select * from grades%1_%2;").arg(id).arg(c2e[fields[i]]));
            query.first();
            query.previous();
            allData.append(query);
        }
    }//获取全部查询的数据

    int row = 0;//表示当前行
    int bias = 0;//偏置
    bool over = false;
    if (fields.contains("姓名"))
        bias = 1;
    while (over == false)
    {
        if (row > ui->tableWidget_0->rowCount() - 1)
            ui->tableWidget_0->insertRow(row);
        if (bias == 1)
        {
            QTableWidgetItem *item = new QTableWidgetItem(name);
            item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
            ui->tableWidget_0->setItem(row, 0, item);
        }
        QString earliestTime = "";
        for (int i = 0; i < allData.size(); i++)//首先遍历一边每个查询结果
        {
            if (allData[i].next())
            {
                QString nowTime = allData[i].value(1).toString();
                if (earliestTime == "")
                    earliestTime = nowTime;
                else if (earliestTime > nowTime)
                    earliestTime = nowTime;
                allData[i].previous();
            }
        }
        for (int i = 0; i < allData.size(); i++)//再遍历一边每个查询结果,填表格
        {
            if (allData[i].next())
            {
                QString nowTime = allData[i].value(1).toString();
                if (earliestTime == nowTime)
                {
                    QTableWidgetItem *item = new QTableWidgetItem(allData[i].value(0).toString());
                    item->setTextAlignment(Qt::AlignHCenter |  Qt::AlignVCenter);
                    ui->tableWidget_0->setItem(row, i + bias, item);
                }
                else
                    allData[i].previous();
            }
        }
        over = true;
        for (int i = 0; i < allData.size(); i++)
        {
            if (allData[i].next())
            {
                over = false;
                allData[i].previous();
                break;
            }
        }
        row++;
    }
    return true;
}

void Home::on_pushButton_1_clicked()
{
    QString id_s = ui->lineEdit_0->text();
    QStringList fields = makeFieldList();
    queryById(id_s, fields);
    return;
}

QStringList Home::makeFieldList()
{
    QStringList fields;
    if (ui->checkBox_0->isChecked() == true)
        fields.append("姓名");
    if (ui->checkBox_1->isChecked() == true)
        fields.append("语文");
    if (ui->checkBox_2->isChecked() == true)
        fields.append("数学");
    if (ui->checkBox_3->isChecked() == true)
        fields.append("英语");
    if (ui->checkBox_4->isChecked() == true)
        fields.append("物理");
    if (ui->checkBox_5->isChecked() == true)
        fields.append("化学");
    if (ui->checkBox_6->isChecked() == true)
        fields.append("生物");
    if (ui->checkBox_7->isChecked() == true)
        fields.append("政治");
    if (ui->checkBox_8->isChecked() == true)
        fields.append("历史");
    if (ui->checkBox_9->isChecked() == true)
        fields.append("地理");
    if (ui->checkBox_10->isChecked() == true)
        fields.append("理综");
    if (ui->checkBox_11->isChecked() == true)
        fields.append("班名次");
    if (ui->checkBox_12->isChecked() == true)
        fields.append("校名次");
    return fields;
}
